<?php
ini_set('error_reporting', 6143);
ini_set('display_errors','1');
define('IN_LC', true);
define('MM_ID', 0);

include_once("validate.php");

if(isset($_GET['id']) && $_GET['id'] == $properties['cron_id'])
{

	if($properties['cron_closepastevents'])
	{
		// Create query
		$q = "UPDATE ". EVENTS_TABLE . "
			  SET e_openSignUps = 0
			  WHERE e_id IN (SELECT e_id FROM " . EVENTDATES_TABLE . "
					   WHERE e_id = ed_event AND e_openSignUps = 1 AND ed_date < NOW()
					   GROUP BY e_id)
			  AND e_id NOT IN(SELECT e_id FROM " . EVENTDATES_TABLE . "
					   WHERE e_id = ed_event AND e_openSignUps = 1 AND ed_date > NOW()
					   GROUP BY e_id)";
	    //echo $q;   
			   
		// Run query
		$db->sql_query($q);
		
		echo 'Closed ' . $db->sql_affectedrows() . ' events';
	}

        if($properties['cron_closepastchamps'])
        {
            // Create query
            $q = "UPDATE ". CHAMPIONSHIPS_TABLE . "
                      SET c_active = 0, c_openSignUps = 0
                      WHERE c_lastRace < NOW()";
            // Run query
            $db->sql_query($q);

            echo 'Closed ' . $db->sql_affectedrows() . ' champs';
        }

        if($properties['cron_createhonours'])
        {
            // Create query
            $q = "INSERT INTO " . HONOURCATS_TABLE . "(hc_title, hc_cat, hc_division, hc_order)
                  SELECT 'Drivers Title', 0, d_id, d_order
                  FROM " . CHAMPIONSHIPS_TABLE . ", " . DIVISIONS_TABLE . "
                      LEFT JOIN " . HONOURCATS_TABLE . " ON d_id = hc_division AND hc_cat = 0
                  WHERE c_id = d_champid AND c_hasDriverTitles = 1 AND hc_division IS NULL";

            // Run query
            $db->sql_query($q);

            echo 'Created ' . $db->sql_affectedrows() . ' Drivers Titles<br/>';


            // Create query
            $q = "INSERT INTO " . HONOURCATS_TABLE . "(hc_title, hc_cat, hc_division, hc_order)
                  SELECT 'Teams Title', 1, d_id, d_order
                  FROM " . CHAMPIONSHIPS_TABLE . ", " . DIVISIONS_TABLE . "
                      LEFT JOIN " . HONOURCATS_TABLE . " ON d_id = hc_division AND hc_cat = 1
                  WHERE c_id = d_champid AND c_hasTeamTitles = 1 AND hc_division IS NULL";

            // Run query
            $db->sql_query($q);

            echo 'Created ' . $db->sql_affectedrows() . ' Team Titles<br/>';



            // Create query
            $q = "INSERT INTO " . HONOURCATS_TABLE . "(hc_title, hc_cat, hc_division, hc_order)
                  SELECT 'Manufacturers Title', 2, d_id, d_order
                  FROM " . CHAMPIONSHIPS_TABLE . ", " . DIVISIONS_TABLE . "
                      LEFT JOIN " . HONOURCATS_TABLE . " ON d_id = hc_division AND hc_cat = 2
                  WHERE c_id = d_champid AND c_hasManufacturerTitles = 1 AND hc_division IS NULL";

            // Run query
            $db->sql_query($q);

            echo 'Created ' . $db->sql_affectedrows() . ' Manufacturer Titles<br/>';
        }


        if($properties['cron_fillhonours'])
        {
            for($x = 1; $x <= 3; $x++)
            {
                // Create query
                $q = "INSERT INTO " . HONOURENTRIES_TABLE . "(he_cat, he_item, he_rank)
                      SELECT hc_id, ct_id, {$x}
                      FROM " . CHAMPIONSHIPS_TABLE . ", " . DIVISIONS_TABLE . ", " . DRIVERSTANDINGS_TABLE . ", " . HONOURCATS_TABLE . "
                          LEFT JOIN " . HONOURENTRIES_TABLE . " ON hc_id = he_cat AND he_rank = {$x}
                      WHERE c_id = d_champid AND hc_division = d_id AND c_lastRace < NOW() AND hc_cat = 0  AND ct_div = d_id AND ct_pos = {$x} AND he_item IS NULL";

                // Run query
                $db->sql_query($q);

                echo 'Created ' . $db->sql_affectedrows() . ' Drivers Honour Champions in pos ' . $x . '<br/>';
            }

            for($x = 1; $x <= 3; $x++)
            {
                // Create query
                $q = "INSERT INTO " . HONOURENTRIES_TABLE . "(he_cat, he_item, he_rank)
                      SELECT hc_id, ct_id, {$x}
                      FROM " . CHAMPIONSHIPS_TABLE . ", " . DIVISIONS_TABLE . ", " . TEAMSTANDINGS_TABLE . ", " . HONOURCATS_TABLE . "
                          LEFT JOIN " . HONOURENTRIES_TABLE . " ON hc_id = he_cat AND he_rank = {$x}
                      WHERE c_id = d_champid AND hc_division = d_id AND c_lastRace < NOW() AND hc_cat = 1 AND ct_div = d_id AND ct_pos = {$x} AND he_item IS NULL";

                // Run query
                $db->sql_query($q);

                echo 'Created ' . $db->sql_affectedrows() . ' Team Honour Champions in pos ' . $x. '<br/>';
            }

            for($x = 1; $x <= 3; $x++)
            {
                // Create query
                $q = "INSERT INTO " . HONOURENTRIES_TABLE . "(he_cat, he_item, he_rank)
                      SELECT hc_id, ct_id, {$x}
                      FROM " . CHAMPIONSHIPS_TABLE . ", " . DIVISIONS_TABLE . ", " . MANUFACTURERSTANDINGS_TABLE . ", " . HONOURCATS_TABLE . "
                          LEFT JOIN " . HONOURENTRIES_TABLE . " ON hc_id = he_cat AND he_rank = {$x}
                      WHERE c_id = d_champid AND hc_division = d_id AND c_lastRace < NOW() AND hc_cat = 2 AND ct_div = d_id AND ct_pos = {$x} AND he_item IS NULL";

                // Run query
                $db->sql_query($q);

                echo 'Created ' . $db->sql_affectedrows() . ' Manufacturer Honour Champions in pos ' . $x. '<br/>';
            }

        }

	if($properties['cron_openevents'])
	{
		// Create query
		$q = "UPDATE ". EVENTS_TABLE . ", " . EVENTDATES_TABLE . "
			  SET e_openSignUps = 1
			  WHERE e_id = ed_event AND e_openSignUps = 0 AND ed_date > NOW() AND ed_date < DATE_ADD(NOW(), INTERVAL " . $properties['cron_openeventstime'] . " DAY)";
	    //echo $q;   

		// Run query
		$db->sql_query($q);

		echo 'Opened ' . $db->sql_affectedrows() . ' events';
	}

	if($properties['cron_addmissingdrivers'])
	{
		// Create query
		$q = "SELECT cs_no, cs_driver, cs_car, cs_team, e_id, cs_div, cs_entryOwner
		 	  FROM " . RACERESULTS_TABLE . ", " . CHAMPSIGNUPS_TABLE . ", ". EVENTS_TABLE . "
			  WHERE cs_driver = r_driver and e_championship = cs_champ and e_id = r_event and
      				r_driver not in (SELECT es_driver FROM " . EVENTSIGNUPS_TABLE . " WHERE es_event = r_event)";
	    //echo $q;   
			   
		// Run query
		$res = $db->sql_query($q);
		$x = 0;
		while ( $row = $db->sql_fetchrow($res) )
		{
			$insert = 'INSERT INTO ' . EVENTSIGNUPS_TABLE . '(es_no, es_driver, es_car, es_team, es_event, es_division, es_signuptime, es_entryOwner)
					   VALUES(' . $row['cs_no'] . ', ' . $row['cs_driver'] . ', ' . $row['cs_car'] . ', ' . $row['cs_team'] . ', ' .
					   		  $row['e_id'] . ', ' . $row['cs_div'] . ', NOW(), ' . $row['cs_entryOwner'] . ')';
			$db->sql_query($insert);
			$x= $x + $db->sql_affectedrows();
		}
		
		echo 'Inserted ' . $x . ' event signups';
	}
	
	
	if($properties['cron_emailchampreminder'])
	{
		include_once('includes/email.php');
	
		// Create query
		$q = "SELECT e_id, e_title, t_fullTitle, t_location, t_logo, e_preevent, e_description, 
						 date_format(ed_date, '%M %e, %Y, %l:%i%p') as ed_dateF,
						 d_title, d_logo, c_logoURL, c_title, d.pf_drivername, u.user_email
			  FROM " . EVENTS_TABLE . ", " . EVENTDATES_TABLE . ", " . DIVISIONS_TABLE . ", " . CHAMPIONSHIPS_TABLE . ", " . 
					   TRACKS_TABLE . ", " . CHAMPSIGNUPS_TABLE . ", " . DRIVERNAME_TABLE . " d, " .  USERSGROUPS_TABLE . " g, " .
					   USERS_TABLE . " u 
			  WHERE e_id = ed_event AND ed_division = d_id AND d_champid = c_id AND e_track = t_id AND cs_div = d_id AND 
					u.user_id = cs_driver AND (pf_champ_notif IS NULL OR pf_champ_notif = 1) AND u.user_id = g.user_id AND 
					g.user_id = d.user_id AND g.group_id = 3 AND DATEDIFF(ed_date, NOW()) = " . $properties['cron_emailchamptime'] . "
					AND e_openSignUps = 1 AND c_enabled = 1 AND cs_driver = cs_entryOwner AND cs_driver NOT IN (
					  SELECT es_driver
					  FROM lc_eventsignups
					  WHERE es_event = e_id)";

		$x = 0;
		// Run query
		$res = $db->sql_query($q);
		while ( $row = $db->sql_fetchrow($res) )
		{
			$email = new email('event_reminder', $row);
			
			$email->addReplacement('{ROUNDNAME}' , 'e_title', 1);
			$email->addReplacement('{TRACKNAME}' , 't_fullTitle', 1);
			$email->addReplacement('{LOCATION}' , 't_location', 1);
			$email->addReplacement('{EMAILADRESS}' , 'user_email', 1);
			$email->addReplacement('{DATE}' , 'ed_dateF', 1);
			$email->addReplacement('{DRIVERNAME}' , 'pf_drivername', 1);
			$email->addReplacement('{EVENTSIGNUPLINK}' , '<a href="' . $www_path . 'events.php?mode=entrants&id=' . $row['e_id'] . '">' . 
														 $www_path . 'events.php?mode=entrants&id=' . $row['e_id'] . '</a>' );
			if($row['c_title'] == $row['d_title'])
			{
				$email->addReplacement('{CHAMPIONSHIP}' , 'c_title', 1);
				$email->addReplacement('{CHAMPIONSHIP2}' , 'c_title', 1);
			}
			else
			{
				$email->addReplacement('{CHAMPIONSHIP}' , $row['c_title'] . '<br>' . $row['d_title']);
				$email->addReplacement('{CHAMPIONSHIP2}' , $row['c_title'] . ' ' . $row['d_title']);
			}
			
			if($row['d_logo'] != '')
			{
				$email->addReplacement('{CHAMPIONSHIPLOGO}' , '<img src="' . $www_path . $row['d_logo'] . '"/>' );
			}
			else if($row['c_logoURL'] != '')
			{
				$email->addReplacement('{CHAMPIONSHIPLOGO}' , '<img src="' . $www_path . $row['c_logoURL'] . '"/>' );
			}
			else
			{
				$email->addReplacement('{CHAMPIONSHIPLOGO}' , '' );
			}
			
			if($row['t_logo'] != '')
			{
				$email->addReplacement('{TRACKLOGO}' , '<img src="' . $www_path . $row['t_logo'] . '"/>' );
			}
			else
			{
				$email->addReplacement('{TRACKLOGO}' , '' );
			}
			
			$email->sendEmail();
			$x++;
		}
		echo 'Sent ' . $x . ' event signup reminders';
	}
	
	if($properties['cron_emailracereminder'])
	{
		include_once('includes/email.php');
	
		// Create query
		$q = "SELECT e_id, e_title, t_fullTitle, t_location, t_logo, e_preevent, e_description, 
						 date_format(ed_date, '%M %e, %Y, %l:%i%p') as ed_dateF,
						 d_title, d_logo, c_logoURL, c_title, d.pf_drivername, u.user_email
			  FROM " . EVENTS_TABLE . ", " . EVENTDATES_TABLE . ", " . DIVISIONS_TABLE . ", " . CHAMPIONSHIPS_TABLE . ", " . 
					   TRACKS_TABLE . ", " . EVENTSIGNUPS_TABLE . ", " . DRIVERNAME_TABLE . " d, " .  USERSGROUPS_TABLE . " g, " .
					   USERS_TABLE . " u 
			  WHERE e_id = ed_event AND ed_division = d_id AND d_champid = c_id AND e_track = t_id AND es_division = d_id AND 
					u.user_id = es_driver AND (pf_event_notif IS NULL OR pf_event_notif = 1) AND u.user_id = g.user_id AND 
					g.user_id = d.user_id AND g.group_id = 3 AND DATEDIFF(ed_date, NOW()) = " . $properties['cron_emailracetime'] . "
					AND e_openSignUps = 1 AND c_enabled = 1 AND  es_event = e_id";
					
		//echo $q;

		$x = 0;
		// Run query
		$res = $db->sql_query($q);
		while ( $row = $db->sql_fetchrow($res) )
		{
			$email = new email('signedup_reminder', $row);
			
			$email->addReplacement('{ROUNDNAME}' , 'e_title', 1);
			$email->addReplacement('{TRACKNAME}' , 't_fullTitle', 1);
			$email->addReplacement('{LOCATION}' , 't_location', 1);
			$email->addReplacement('{EMAILADRESS}' , 'user_email', 1);
			$email->addReplacement('{DATE}' , 'ed_dateF', 1);
			$email->addReplacement('{DRIVERNAME}' , 'pf_drivername', 1);
			$email->addReplacement('{MYEVENTSLINK}' , '<a href="' . $www_path . 'usercp.php?action=mycurrentevents">' . 
														 $www_path . 'usercp.php?action=mycurrentevents</a>' );
			if($row['c_title'] == $row['d_title'])
			{
				$email->addReplacement('{CHAMPIONSHIP}' , 'c_title', 1);
				$email->addReplacement('{CHAMPIONSHIP2}' , 'c_title', 1);
			}
			else
			{
				$email->addReplacement('{CHAMPIONSHIP}' , $row['c_title'] . '<br>' . $row['d_title']);
				$email->addReplacement('{CHAMPIONSHIP2}' , $row['c_title'] . ' ' . $row['d_title']);
			}
			
			if($row['d_logo'] != '')
			{
				$email->addReplacement('{CHAMPIONSHIPLOGO}' , '<img src="' . $www_path . $row['d_logo'] . '"/>' );
			}
			else if($row['c_logoURL'] != '')
			{
				$email->addReplacement('{CHAMPIONSHIPLOGO}' , '<img src="' . $www_path . $row['c_logoURL'] . '"/>' );
			}
			else
			{
				$email->addReplacement('{CHAMPIONSHIPLOGO}' , '' );
			}
			
			if($row['t_logo'] != '')
			{
				$email->addReplacement('{TRACKLOGO}' , '<img src="' . $www_path . $row['t_logo'] . '"/>' );
			}
			else
			{
				$email->addReplacement('{TRACKLOGO}' , '' );
			}
						
			$email->sendEmail();
			$x++;
		}
		echo 'Sent ' . $x . ' you are signed up reminders';
	}
	
	
	if($properties['cron_banwarningemail'])
	{
		include_once('includes/email.php');
	
		// Create query
		$q = "SELECT u_id, pf_drivername, u_licensePoints, u_license, bt_points, bt_duration, u.user_email, l_name, bt_license
			  FROM " . LCUSERS_TABLE . ", " . BANTHRESHOLDS_TABLE . ", " . DRIVERNAME_TABLE . " p, " . USERS_TABLE . " u, " . LICENSES_TABLE . "
			  WHERE (bt_license = 0 OR bt_license = u_license) AND u_licensePoints < bt_points and u_licensePoints > bt_points - " . $properties['cron_banwarningpoints'] . " AND
			        p.user_id = u.user_id AND p.user_id = u_id AND bt_points > 0 AND bt_points != u_emailBanWarning AND l_id = u_license AND
			        bt_points NOT IN (SELECT b_licensePoints FROM " . BANS_TABLE . " WHERE b_user = u_id AND b_licensePoints = bt_points AND b_banCleared = 0)";
	  	//echo $q;

		$x = 0;
		// Get users to send email
		$res = $db->sql_query($q);
		
		//update table 
		if($db->sql_numrows($res) > 0)
		{
			// Create query
			$q = "UPDATE " . LCUSERS_TABLE . ", " . BANTHRESHOLDS_TABLE . ", " . DRIVERNAME_TABLE . " p, " . USERS_TABLE . " u, " . LICENSES_TABLE . "
				  SET u_emailBanWarning = bt_points, u_emailBanClear = bt_cleared
				  WHERE (bt_license = 0 OR bt_license = u_license) AND u_licensePoints < bt_points and u_licensePoints > bt_points - " . $properties['cron_banwarningpoints'] . " AND
						p.user_id = u.user_id AND p.user_id = u_id AND bt_points > 0 AND bt_points != u_emailBanWarning AND l_id = u_license AND
						bt_points NOT IN (SELECT b_licensePoints FROM " . BANS_TABLE . " WHERE b_user = u_id AND b_licensePoints = bt_points AND b_banCleared = 0)";
			//echo $q;
			 $db->sql_query($q);
		}
		
		while ( $row = $db->sql_fetchrow($res) )
		{
			$email = new email('warning_near_ban', $row);
			
			$email->addReplacement('{EMAILADRESS}', 'user_email', 0);
			$email->addReplacement('{USER_ID}' , 'u_id', 1);
			$email->addReplacement('{CURRENT_POINTS}' , 'u_licensePoints', 1);
			$email->addReplacement('{BAN_POINTS}' , 'bt_points', 1);
			$email->addReplacement('{BAN_DURATION}' , 'bt_duration', 1);
			$email->addReplacement('{CURRENT_LICENSE}' , 'l_name', 1);
			$email->addReplacement('{DRIVERNAME}' , 'pf_drivername', 1);
			$email->addReplacement('{LICENSELINK}' , $www_path . 'drivers.php?mode=license&id=' . $row['u_id']);
			
			
			if($row['bt_license'] == 0)
				$email->addReplacement('{BAN_EVENTS}' , $lang['EmailWarningBanNearAll']);
			else
				$email->addReplacement('{BAN_EVENTS}' , 'l_name', 1);
				
				
			$email->sendEmail();
			$x++;
		}
		echo 'Sent ' . $x . ' near to ban warnings';
	}
	
	//Update Users
	$qRanking = 'UPDATE ' . LCUSERS_TABLE . '
				 SET u_rankingPoints = (
					   SELECT COALESCE(round(sum((EXP(0 - SQRT(DATEDIFF(NOW(), ed_date) + 1)/30)) *
                                                            ((d_ranking * d_ranking / 300) * (10 / r_raceposition + 1 / r_qualifyingrank + 1 / r_fastestlap_rank))),2), 0)
					   FROM ' . RACERESULTS_TABLE . ', ' . EVENTSIGNUPS_TABLE . ', ' . DIVISIONS_TABLE . ', ' . EVENTDATES_TABLE . '
					   WHERE u_id = r_driver AND r_event = es_event AND es_driver = r_driver AND es_division = d_id AND ed_division = d_id AND ed_event = es_event),
					u_licensePoints = (SELECT COALESCE(SUM(ar_licensepoints),0) FROM ' . INCIDENTADMINREVIEW_TABLE . ', ' . INCIDENTS_TABLE . ' 
									   WHERE ar_incident = in_id AND in_adminstatus = 3 AND ar_admin = 0 AND ar_licenseexpiry > NOW() AND ar_driver = u_id),
					u_races = (SELECT COALESCE(COUNT(*),0) FROM ' . RACERESULTS_TABLE . ' WHERE r_driver = u_id)';
	$db->sql_query($qRanking);
	
	//Clear old bans where users have dropped below the ban rating
	$qClearBans = 'update ' . LCUSERS_TABLE . ', ' . BANS_TABLE . ' set b_banCleared = 1 where b_user = u_id and u_licensePoints < b_clearedPoints';
	$db->sql_query($qClearBans);
	
	//Clear old ban warning notifications
	$qClearBans = 'update ' . LCUSERS_TABLE . '  set u_emailBanWarning = 0, u_emailBanClear = 0 where u_emailBanClear > u_licensePoints';
	$db->sql_query($qClearBans);
	
	//Promote user licenses.
	$q =  'SELECT * FROM ' . LICENSES_TABLE . ' WHERE l_default = 0 ORDER BY l_order DESC';
	$res = $db->sql_query($q);
	while ( $license = $db->sql_fetchrow($res) )
	{
		$qDrivers = 'SELECT u_id, pf_drivername, u_license,
    			(SELECT count(*) FROM ' . RACERESULTS_TABLE . ' WHERE r_driver = u_id) as racecount,
			    (SELECT sum(ar_licensepoints) FROM ' . INCIDENTADMINREVIEW_TABLE . ' WHERE ar_driver = u_id AND ar_licenseexpiry > NOW()) as pointscount
			  FROM ' . LCUSERS_TABLE . ', ' . DRIVERNAME_TABLE . ', ' . LICENSES_TABLE . '
			  WHERE user_id = u_id AND u_license = l_id AND l_order > ' . $license['l_order'] . '
			  HAVING racecount >= ' . $license['l_minRaces'] . ' AND (pointscount <= ' . $license['l_maxPoints'] . ' OR pointscount IS NULL)
			  ORDER BY racecount DESC';
		$resDrivers = $db->sql_query($qDrivers);
		while ( $driver = $db->sql_fetchrow($resDrivers) )
		{
			$qUpd = 'UPDATE ' . LCUSERS_TABLE . ' SET u_license = ' . $license['l_id'] . ' WHERE u_id = ' . $driver['u_id'];
			$db->sql_query($qUpd);
		}
	}

	//
}

exit();
?>